﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaint_FullIndexStats]
#-- Purpose:		Rebuilds all indexes (clustered, then non-clustered) and
#--					then runs all stats updates
#--	Last Update:	09/26/2014
#--					For a complete history - please review comments in Version
#--					control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaint_FullIndexStats]
(
	@database_name		nvarchar(256),
	@include_indexes	bit		= 1,
	@include_stats		bit		= 1,
	@fullscan_stats		bit		= 1
)
AS

SET NOCOUNT ON

DECLARE @sTemplateIndex nvarchar(MAX),
		@sTemplateCompress nvarchar(MAX),
		@sTemplateStats nvarchar(MAX),
		@sCommand nvarchar(MAX),
		@sSQL nvarchar(MAX),
		@iVersion int,
		@sCompress nvarchar(500),
		@sCompressCompare nvarchar(500);
DECLARE @t TABLE (
	[object_name]		nvarchar(256),
	[schema_name]		nvarchar(256),
	[index_name]		nvarchar(256),
	[last_stats_date]	datetime,
	[isIndex]			bit,
	[index_type]		tinyint,
	[compression]		varchar(50)
);

SELECT	@sTemplateIndex = 'USE [VAR_DBNAME]; ALTER INDEX [VAR_INDEXNAME] ON [VAR_TABLENAME] REBUILD PARTITION = ALL[VAR_DATACOMPRESS]',
		@sTemplateCompress = ' WITH (DATA_COMPRESSION = [VAR_COMPRESSTYPE])',
		@sTemplateStats = 'USE [VAR_DBNAME]; UPDATE STATISTICS [VAR_TABLENAME] WITH FULLSCAN',
		@iVersion = CONVERT(int, (CASE	WHEN PARSENAME(CONVERT(varchar, SERVERPROPERTY('productversion'), 0), 4) IS NULL 
										THEN PARSENAME(CONVERT(varchar, SERVERPROPERTY('productversion'), 0), 3) 
										ELSE PARSENAME(CONVERT(varchar, SERVERPROPERTY('productversion'), 0), 4) 
										END), 0),
		@sCompress = (CASE WHEN @iVersion > 9 THEN 'p.[data_compression_desc]' ELSE 'NULL [data_compression_desc]' END),
		@sCompressCompare = (CASE WHEN @iVersion > 9 THEN 'p.[data_compression] <> 0' ELSE '1 = 0' END);

SELECT	@sSQL = 'USE [VAR_DBNAME];
WITH statInd AS (
	SELECT		[object_id] [object_id],
				[index_id],
				[name],
				CAST(1 as bit) [isIndex],
				[type] [indexType]
	FROM		[sys].[indexes]
	UNION
	SELECT		[object_id] [object_id],
				[stats_id] [index_id],
				[name],
				CAST(0 as bit) [isIndex],
				NULL
	FROM		[sys].[stats]
)
SELECT		OBJECT_NAME(t.[object_id]),
			SCHEMA_NAME(t.[schema_id]),
			i.[name],
			STATS_DATE(t.[object_id], i.[index_id]) [lastStatsDate],
			ISNULL(i.[isIndex], 0) [isIndex],
			i.[indexType],
			[VAR_COMPRESS]
FROM		sys.tables t
JOIN		(
			SELECT		t1.[object_id],
						t1.[index_id],
						t1.[name],
						t2.[isIndex],
						t2.[indexType]
			FROM		statInd t1
			LEFT JOIN	statInd t2 ON t1.[object_id] = t2.[object_id]
						AND t1.[index_id] = t2.[index_id]
						AND t2.[isIndex] = 1
			WHERE		t1.[isIndex] = 0
			) i ON i.[object_id] = t.[object_id]
LEFT JOIN	[sys].[partitions] p ON i.[object_id] = p.[object_id]
			AND i.[index_id] = p.[index_id]
			AND [VAR_COMPRESSCOMPARE]
WHERE		t.[type] = ''U''
			AND NOT t.[name] = ''__RefactorLog''
ORDER BY	t.[name],
			i.[index_id]',
		@sSQL = REPLACE(REPLACE(REPLACE(@sSQL, '[VAR_COMPRESS]', @sCompress), '[VAR_COMPRESSCOMPARE]', @sCompressCompare), '[VAR_DBNAME]', @database_name);

INSERT INTO @t
	EXEC	(@sSQL);

IF @include_indexes = 1
  BEGIN
	--- Rebuild Clustered Indexes
	DECLARE crs CURSOR FOR
		SELECT	REPLACE(
					REPLACE(
						REPLACE(
							REPLACE(@sTemplateIndex, '[VAR_INDEXNAME]', QUOTENAME([index_name]))
						, '[VAR_TABLENAME]', QUOTENAME([schema_name]) + '.' + QUOTENAME([object_name]))
					, '[VAR_DATACOMPRESS]', ISNULL(REPLACE(@sTemplateCompress, '[VAR_COMPRESSTYPE]', [compression]), SPACE(0)))
				, '[VAR_DBNAME]', @database_name)
		FROM	@t
		WHERE	[isIndex] = 1
				AND [index_type] = 1
	OPEN crs
	FETCH NEXT FROM crs INTO @sCommand
	WHILE @@FETCH_STATUS <> -1
	  BEGIN
		RAISERROR (@sCommand, 0, 1) WITH NOWAIT;
		EXEC	(@sCommand)
		FETCH NEXT FROM crs INTO @sCommand
	  END
	CLOSE crs
	DEALLOCATE crs

	--- Rebuild Nonclustered Indexes
	DECLARE crs CURSOR FOR
		SELECT	REPLACE(
					REPLACE(
						REPLACE(
							REPLACE(@sTemplateIndex, '[VAR_INDEXNAME]', QUOTENAME([index_name]))
						, '[VAR_TABLENAME]', QUOTENAME([schema_name]) + '.' + QUOTENAME([object_name]))
					, '[VAR_DATACOMPRESS]', ISNULL(REPLACE(@sTemplateCompress, '[VAR_COMPRESSTYPE]', [compression]), SPACE(0)))
				, '[VAR_DBNAME]', @database_name)
		FROM	@t
		WHERE	[isIndex] = 1
				AND [index_type] <> 1
	OPEN crs
	FETCH NEXT FROM crs INTO @sCommand
	WHILE @@FETCH_STATUS <> -1
	  BEGIN
		RAISERROR (@sCommand, 0, 1) WITH NOWAIT;
		EXEC	(@sCommand)
		FETCH NEXT FROM crs INTO @sCommand
	  END
	CLOSE crs
	DEALLOCATE crs
  END

IF @include_stats = 1
  BEGIN
	--- Update Statistics
	DECLARE crs CURSOR FOR
		SELECT	DISTINCT
				REPLACE(
					REPLACE(@sTemplateStats, '[VAR_TABLENAME]', QUOTENAME([schema_name]) + '.' + QUOTENAME([object_name]))
				, '[VAR_DBNAME]', @database_name)
		FROM	@t
		WHERE	[isIndex] = 0
	OPEN crs
	FETCH NEXT FROM crs INTO @sCommand
	WHILE @@FETCH_STATUS <> -1
	  BEGIN
		RAISERROR (@sCommand, 0, 1) WITH NOWAIT;
		EXEC	(@sCommand)
		FETCH NEXT FROM crs INTO @sCommand
	  END
	CLOSE crs
	DEALLOCATE crs
  END

SET NOCOUNT OFF